create or replace package pkg_stock 
is
	procedure insert_sys_stats (
		p_seq_id				number,
		p_channel_id			varchar2,
		p_processing_time 		number,
		p_start_time			date,
		p_trading_date			date,
		p_status				varchar2,
		p_message 				varchar2 := null);
	
	procedure insert_market_info (
		p_seq_id				number,
		p_channel_id			varchar2,
		p_channel_priority		number,
		p_sec_center 			varchar2,
		p_market_index 			number,
		p_index_change 			number,
		p_index_change_pct 		number,
		p_trading_qualtity		number := null,
		p_trading_volume		number,
		p_trading_value			number,
		p_status				varchar2 := null,
		p_session_no			number := null,
		p_trading_date		  	date);
		
	procedure insert_stock_info (
		p_seq_id				number,
		p_channel_id		varchar2,
		p_stock_symbol			varchar2,
		p_description			varchar2 := null,
		p_basic_price			number := null,
		p_ceiling_price			number := null,
		p_floor_price			number := null,
		p_offer_price1			number := null,
		p_offer_volume1			number := null,
		p_bid_price1			number := null,
		p_bid_volume1			number := null,
		p_offer_price2			number := null,
		p_offer_volume2			number := null,
		p_bid_price2			number := null,
		p_bid_volume2			number := null,
		p_offer_price3			number := null,
		p_offer_volume3			number := null,
		p_bid_price3			number := null,
		p_bid_volume3 			number := null,
		p_match_price			varchar2 := null,
		p_match_volume			number := null,
		p_match_change			number := null,
		p_open_price			number := null,
		p_close_price			number := null,
		p_highest_price			number := null,
		p_lowest_price			number := null);
		
	procedure update_stock_data_history;
end;

/

create or replace package body pkg_stock 
is
	procedure insert_sys_stats (
		p_seq_id				number,
		p_channel_id			varchar2,
		p_processing_time 		number,
		p_start_time			date,
		p_trading_date			date,
		p_status				varchar2,
		p_message 				varchar2 := null)
	is
	begin
		insert into sys_stats
			(
				stats_id,
				seq_id,
				channel_id,
				processing_time,
				start_time,
				trading_date,
				status,
				message
			) values
			(
				sys_stats_seq.nextval,
				p_seq_id,
				p_channel_id,
				p_processing_time,
				p_start_time,
				p_trading_date,
				p_status,
				p_message
			);
	end;
		
	procedure insert_market_info (
		p_seq_id				number,
		p_channel_id			varchar2,
		p_channel_priority		number,
		p_sec_center 			varchar2,
		p_market_index 			number,
		p_index_change 			number,
		p_index_change_pct 		number,
		p_trading_qualtity		number := null,
		p_trading_volume		number,
		p_trading_value			number,
		p_status				varchar2 := null,
		p_session_no			number := null,
		p_trading_date		  	date)
	is
	begin
		insert into stock_market_info 
		(
			seq_id,
			channel_id,
			channel_priority,
			sec_center,
			market_index,
			index_change,
			index_change_pct,
			trading_qualtity,
			trading_volume,
			trading_value,
			status,
			session_no,
			trading_date
		) values 
		(
			p_seq_id,
			p_channel_id,
			p_channel_priority,
			p_sec_center,
			p_market_index,
			p_index_change,
			p_index_change_pct,
			p_trading_qualtity,
			p_trading_volume,
			p_trading_value,
			p_status,
			p_session_no,
			p_trading_date
		);
	end;
		
	procedure insert_stock_info (
		p_seq_id				number,
		p_channel_id		varchar2,
		p_stock_symbol			varchar2,
		p_description			varchar2,
		p_basic_price			number := null,		
		p_ceiling_price			number := null,
		p_floor_price			number := null,
		p_offer_price1			number := null,
		p_offer_volume1			number := null,
		p_bid_price1			number := null,
		p_bid_volume1			number := null,
		p_offer_price2			number := null,
		p_offer_volume2			number := null,
		p_bid_price2			number := null,
		p_bid_volume2			number := null,
		p_offer_price3			number := null,
		p_offer_volume3			number := null,
		p_bid_price3			number := null,
		p_bid_volume3 			number := null,
		p_match_price			varchar2 := null,
		p_match_volume			number := null,
		p_match_change			number := null,
		p_open_price			number := null,
		p_close_price			number := null,
		p_highest_price			number := null,
		p_lowest_price			number := null)
	is
	begin
		insert into stock_info 
			(
				seq_id,
				channel_id,
				stock_symbol,
				description,
				basic_price,
				ceiling_price,
				floor_price,
				offer_price1,
				offer_volume1,
				bid_price1,
				bid_volume1,
				offer_price2,
				offer_volume2,
				bid_price2,
				bid_volume2,
				offer_price3,
				offer_volume3,
				bid_price3,
				bid_volume3,
				match_price,
				match_volume,
				match_change,
				open_price,
				close_price,
				highest_price,
				lowest_price
			) values 
			(
				p_seq_id,
				p_channel_id,
				p_stock_symbol,
				p_description,
				p_basic_price,
				p_ceiling_price,
				p_floor_price,
				p_offer_price1,
				p_offer_volume1,
				p_bid_price1,
				p_bid_volume1,
				p_offer_price2,
				p_offer_volume2,
				p_bid_price2,
				p_bid_volume2,
				p_offer_price3,
				p_offer_volume3,
				p_bid_price3,
				p_bid_volume3,
				p_match_price,
				p_match_volume,
				p_match_change,
				p_open_price,
				p_close_price,
				p_highest_price,
				p_lowest_price
			);
	end;
	
	procedure update_stock_data_history
	is
	begin
		for rec in (
		select sm.seq_id,sm.channel_id
		from (
			select min(channel_priority) as chan_pri,trading_date 
			from stock_market_info group by (trading_date)) a,stock_market_info sm 
		where sm.channel_priority = a.chan_pri 
			and sm.trading_date = a.trading_date
		order by sm.trading_date
		) loop
			insert into stock_market_info_history 
			select  stock_market_info_history_seq.nextval,
					sec_center,
					market_index,
					index_change,
					index_change_pct,
					trading_qualtity,
					trading_volume,
					trading_value,
					status,
					session_no,
					trading_date
			from stock_market_info where seq_id = rec.seq_id and channel_id = rec.channel_id;
			
			insert into stock_info_history 
			select stock_market_info_history_seq.currval,
					stock_symbol,
					basic_price,
					ceiling_price,
					floor_price,
					offer_price1,
					offer_volume1,
					bid_price1,
					bid_volume1,
					offer_price2,
					offer_volume2,
					bid_price2,
					bid_volume2,
					offer_price3,
					offer_volume3,
					bid_price3,
					bid_volume3,
					match_price,
					match_volume,
					match_change,
					open_price,
					close_price,
					highest_price,
					lowest_price,
					description
			from stock_info where seq_id = rec.seq_id and channel_id = rec.channel_id;
			commit;
			
		end loop;
		execute immediate 'truncate table stock_market_info';
		execute immediate 'truncate table stock_info';
	end;
end;

/
